﻿using System;
using System.Collections.Generic;
using System.Web;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// DBHelper 的摘要说明
/// </summary>
public class DBHelper
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    static string connectionString = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
    /// <summary>
    /// 数据库连接对象
    /// </summary>
    static SqlConnection conne = null;

    static DBHelper()
    {
        conne = new SqlConnection(connectionString);
    }
    public static DataTable GetDataTable(string text)
    {
        SqlConnection conn = new SqlConnection(connectionString);
        conn.Open();

        SqlDataAdapter odda = new SqlDataAdapter(text, conn);

        DataSet ds = new DataSet();
        odda.Fill(ds);
        conn.Close();
        return ds.Tables[0];
    }
    public static int ExecuteCommand(string text)
    {
        SqlConnection conn = new SqlConnection(connectionString);
        conn.Open();
        SqlCommand cmd = new SqlCommand(text, conn);
        int i = cmd.ExecuteNonQuery();
        conn.Close();
        return i;
    }
    /// <summary>
    /// 读取DataTable数据
    /// </summary>
    /// <param name="text">sql语句，（带参数的完整的select语句，参数自定义）</param>
    /// <param name="param">参数数组，必须与sql参数相对应</param>
    /// <returns></returns>
    public static DataTable GetDataTable(string text, params SqlParameter[] param)
    {
        SqlDataAdapter odda = new SqlDataAdapter(text, conne);
        if (conne.State != ConnectionState.Open)
            conne.Open();
        if (param != null || param.Length > 0)
            odda.SelectCommand.Parameters.AddRange(param);
        DataSet ds = new DataSet();
        odda.Fill(ds);
        if (conne.State != ConnectionState.Closed)
            conne.Close();
        return ds.Tables[0];
    }
    /// <summary>
    /// 读取DataTable数据
    /// </summary>
    /// <param name="text">sql语句，（带参数的完整的select语句，参数必须从@0,开始）</param>
    /// <param name="param">参数数组</param>
    /// <returns></returns>
    public static DataTable GetDataTable(string text, params object[] param)
    {
        using (SqlDataAdapter odda = new SqlDataAdapter(text, conne))
        {
            if (conne.State != ConnectionState.Open)
                conne.Open();
            try
            {
                if (param != null || param.Length > 0)
                {
                    for (int i = 0; i < param.Length; i++)
                    {
                        SqlParameter sp = new SqlParameter("@" + i.ToString(), param[i]);
                        odda.SelectCommand.Parameters.Add(sp);
                    }
                }
                DataSet ds = new DataSet();
                odda.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                if (conne.State != ConnectionState.Closed)
                    conne.Close();
            }
        }
    }
    /// <summary>
    /// 返回会受影响行数
    /// </summary>
    /// <param name="cmdText">T-SQL命令（update,insert,delete语句）</param>
    /// <param name="param">参数列表（可为空null）</param>
    /// <returns></returns>
    public static int ExecuteCommand(string cmdText, params SqlParameter[] param)
    {
        SqlCommand cmd = new SqlCommand(cmdText, conne);
        if (param != null || param.Length > 0)
            cmd.Parameters.AddRange(param);
        if (conne.State != ConnectionState.Open)
            conne.Open();
        int j = cmd.ExecuteNonQuery();
        if (conne.State != ConnectionState.Closed)
            conne.Close();
        return j;
    }
    /// <summary>
    /// 返回会受影响行数
    /// </summary>
    /// <param name="cmdText">T-SQL命令（update,insert,delete语句）</param>
    /// <param name="param">参数列表（可为空null）</param>
    /// <returns></returns>
    public static int ExecuteCommand(string cmdText, params object[] param)
    {
        SqlCommand cmd = new SqlCommand(cmdText, conne);
        if (param != null || param.Length > 0)
        {
            for (int i = 0; i < param.Length; i++)
            {
                SqlParameter sp = new SqlParameter("@" + i.ToString(), param[i]);
                cmd.Parameters.Add(sp);
            }
        }
        if (conne.State != ConnectionState.Open)
        {
            conne.Open();
        }
        int j = cmd.ExecuteNonQuery();
        if (conne.State != ConnectionState.Closed)
            conne.Close();
        return j;
    }
}